package bookmarks;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Optional;

import org.springframework.stereotype.Component;

@Component("AccountRepository")
public class AccountMysqlRepository implements AccountRepository {

    @Override
    public Optional<Account> findByUsername(final String inputUsername) {

        Connection connection = null;
        Optional<Account> account = Optional.empty();
        try {
            connection = this._openConnection();
            account = _findByUsername(connection, inputUsername);
        } catch (Exception e) {
            // TODO: log
        } finally {
            this._closeCollection(connection);
            return account;
        }
    }

    @Override
    public Optional<Account> save(final Account inputAccount) {
        
        Connection connection = null;
        Optional<Account> outputAccount = Optional.empty();
        try {
            connection = this._openConnection();
            outputAccount = this._saveAccount(connection, inputAccount);
        } catch (Exception e) {
            // TODO: log
        } finally {
            this._closeCollection(connection);
            return outputAccount;
        }
    }

    private Connection _openConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        return DriverManager.getConnection("jdbc:mysql://localhost/bookmarks", "user", "");
    }

    private void _closeCollection(Connection connection) {
        try {
            connection.close();
        } catch (Exception e) {
            // TODO: log
        }
    }

    private Optional<Account> _findByUsername(final Connection connection, final String inputUsername) throws Exception {
        // TODO: rewrite in jOOQ
        final PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Account WHERE username = ?");
        preparedStatement.setString(1, inputUsername);
        final ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            preparedStatement.close();
            return Optional.empty();
        }
        final Long id = resultSet.getLong("id");
        final String username = resultSet.getString("username");
        resultSet.close();
        preparedStatement.close();
        return Optional.of(new Account(id, username));
    }

    private Optional<Account> _saveAccount(final Connection connection, final Account inputAccount) throws Exception {
        // ignore id, use auto generated id
        // TODO: rewrite in jOOQ
        final PreparedStatement insertStatement = connection.prepareStatement("INSERT INTO Account VALUES (default, ?)");
        insertStatement.setString(1, inputAccount.getUsername());
        insertStatement.executeUpdate();
        insertStatement.close();

        final PreparedStatement getIdStatement = connection.prepareStatement("SELECT LAST_INSERT_ID() FROM Account");
        final ResultSet resultSet = getIdStatement.executeQuery();
        if (!resultSet.next()) {
            resultSet.close();
            getIdStatement.close();
            return Optional.empty();
        }
        final Long id = resultSet.getLong(1);
        resultSet.close();
        getIdStatement.close();
        return Optional.of(new Account(id, inputAccount.getUsername()));
    }


    
}